import pymysql, hashlib, xlrd


class register:
    def __init__(self):
        self.db = pymysql.connect('localhost', 'root', '', database='phpwind', charset='utf8')
        self.cur = self.db.cursor()
        self.cur.execute('use phpwind')

    def dbconnect(self):
        lists = self.excel()
        print(lists)
        for i in lists:
            username = i[0]
            pwd = i[1]
            password = self.setpassword(pwd)
            email = i[2]
            sql = "insert into pw_members(username,password,email,memberid,signature,introduce,aliww,banpm) values ('%s','%s','%s',8,'' ,'' ,'' ,'')" % (
            username, password, email)
            print('测试', sql)
            self.cur.execute(sql)
        self.db.commit()
        self.db.close()

    def setpassword(self, pwd):
        md5 = hashlib.md5()
        sign_str = pwd
        sign_bytes_utf8 = sign_str.encode()
        md5.update(sign_bytes_utf8)
        sign_md5 = md5.hexdigest()
        # print(sign_md5)
        return sign_md5

    def excel(self):
        data = xlrd.open_workbook('users.xls')
        table = data.sheets()[0]
        lists = []
        for i in range(1, table.nrows):
            li = table.row_values(i)
            lists.append(li[0:])
        return lists


register().dbconnect()
